package com.makefuture.utils.Excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiExcelUtil {

	/**
	 * 读取Excel文件，支持.xls和.xlsx2种版本
	 * @return List<Object><br/>
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public static List<Object> readExcel(String file) throws FileNotFoundException, IOException{
		Workbook workbook = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		if(file.indexOf("xlsx") != -1){
			workbook = new XSSFWorkbook(new FileInputStream(file));
		}else if(file.indexOf("xls") != -1){
			workbook = new HSSFWorkbook(new FileInputStream(file));
		}else{
			System.out.println("该文件不是excel文件！");
			return null;
		}
		sheet = workbook.getSheetAt(0);
		//获取sheet的总行数
		int rowsNum = sheet.getLastRowNum();
		//获取sheet的总列数
		int colsNum = sheet.getRow(0).getLastCellNum();

		String cellValue ;
		//获取row
		for(int i = 0 ; i < rowsNum ; i++){
			row = sheet.getRow(i);
			System.out.print("行号： " + row.getRowNum());
			for(int j = 0 ; j < colsNum ; j++){
				cell = row.getCell(j);
				//将单元格设置为String，取的时候再转
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cellValue = cell.getStringCellValue();
				System.out.print("\t" + cellValue);
			}
			System.out.println();
		}
		return null;
	}

	
	/**
	 * 创建Excel文件,支持.xls和.xlsx2种类型<br/>
	 * 示例：file = "d:\\helloworld.xls"
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public static void createxlsExcel(List<Student> rowList, Map<String,Integer> map, String file) throws FileNotFoundException, IOException{
		//数据可以没有，列一定要有
		if(map == null || map.size() == 0){
			return;
		}
		Workbook workbook = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		if(file.indexOf("xlsx") != -1){
			workbook = new XSSFWorkbook();
		}else if(file.indexOf("xls") != -1){
			workbook = new HSSFWorkbook();
		}else{
			System.out.println("excel文件的后缀名是xls或xlsx！请检查！");
			return;
		}
		sheet = workbook.createSheet();
		//列头样式
		Font fontColumn = workbook.createFont();
		CellStyle cellStyleColumn = workbook.createCellStyle();
		fontColumn.setFontHeightInPoints((short) 10);
		fontColumn.setColor(IndexedColors.BLACK.getIndex());
		fontColumn.setBoldweight(Font.BOLDWEIGHT_BOLD);
		cellStyleColumn.setFont(fontColumn);
		cellStyleColumn.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyleColumn.setBorderRight(CellStyle.BORDER_THIN);
		cellStyleColumn.setBorderTop(CellStyle.BORDER_THIN);
		cellStyleColumn.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyleColumn.setAlignment(CellStyle.ALIGN_CENTER);
		
		//内容样式
		Font fontContent = workbook.createFont();
		CellStyle cellStyleContent = workbook.createCellStyle();
		fontContent.setFontHeightInPoints((short) 10);
		fontContent.setColor(IndexedColors.BLACK.getIndex());
		fontContent.setBoldweight(Font.DEFAULT_CHARSET);
		cellStyleContent.setFont(fontContent);
		cellStyleContent.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyleContent.setBorderRight(CellStyle.BORDER_THIN);
		cellStyleContent.setBorderTop(CellStyle.BORDER_THIN);
		cellStyleContent.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyleContent.setAlignment(CellStyle.ALIGN_CENTER);

		// 创建列行并设置名称
		Row columnRow = sheet.createRow(0);
		int m = 0;
		for(String s : map.keySet()){
			sheet.setColumnWidth(m,map.get(s));
			Cell columnCell = columnRow.createCell(m);
			columnCell.setCellValue(s);
			columnCell.setCellStyle(cellStyleColumn);
			m++;
		}
		
		// 设置每行每列的值
		for (int i = 0; i < rowList.size(); i++) {
			// Row 和 Cell 都是从0开始计数的，0行用在了列头显示，所以i从1开始
			row = sheet.createRow(i+1);
			
			// 在row行上创建一个方格
			cell = row.createCell(0);
			cell.setCellValue(rowList.get(i).getSid());
			cell.setCellStyle(cellStyleContent);
			
			cell = row.createCell(1);
			cell.setCellValue(rowList.get(i).getName());
			cell.setCellStyle(cellStyleContent);
			
			cell = row.createCell(2);
			cell.setCellValue(rowList.get(i).getSex());
			cell.setCellStyle(cellStyleContent);
			
			//如果值的类型为时间，可用此格式化时间
			//cellStyleContent.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd")); 
		}
		OutputStream outputStream = new FileOutputStream(file);
		workbook.write(outputStream);
		outputStream.close();
	}
	
	
	
	
	public static void main(String[] args) throws Exception{
		//如果需要复杂的单元格，下面的2行代码可以合并单元格
		//CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 3, 0, 3)
		//sheet.addMergedRegion(cellRangeAddress);
		
		//readExcel("src\\file\\学生.xlsx");
		
//		List<Student> rowList = new ArrayList<Student>();
//		Student s1 = new Student();
//		s1.setSid(1);
//		s1.setName("张三");
//		s1.setSex("男");
//		Student s2 = new Student();
//		s2.setSid(23);
//		s2.setName("李四");
//		s2.setSex("男");
//		Student s3 = new Student();
//		s3.setSid(3);
//		s3.setName("王老五");
//		s3.setSex("女");
//		
//		rowList.add(s1);
//		rowList.add(s2);
//		rowList.add(s3);
//		
//		Map<String,Integer> map = new LinkedHashMap<String, Integer>();
//		map.put("学号", 5000);
//		map.put("姓名", 5000);
//		map.put("性别", 5000);
//		createxlsExcel(rowList, map, "src\\file\\学生.xlsx");
	}
	
}
